/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package botoseis.mainGui.database;

import java.awt.Color;
import java.awt.Font;
import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.DecimalFormat;
import javax.swing.DefaultListModel;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import net.proteanit.sql.DbUtils;

/**
 *
 * @author wogonowski
 */
public class DBImport extends javax.swing.JFrame {
 
    Connection con=null;
    ResultSet rs = null;
    PreparedStatement pst = null;
    /**
     * Creates new form DBImport
     */
    public DBImport() {
        initComponents();
        modelP = new DefaultListModel();
        modelL = new DefaultListModel();
        modelAT = new DefaultListModel();
        setTitle("Database Utlilities");
        jTable1.getTableHeader().setFont(new Font("Arial",Font.BOLD,12));
        jTable1.getTableHeader().setForeground(Color.WHITE);
        jTable1.getTableHeader().setBackground(new Color(80,113,220));
        //Fill jList1
        jList1.setModel(modelP);
        jList2.setModel(modelL);
        jList3.setModel(modelAT);
        con=DBConnect.ConnectDb();
         
             try {  
                   modelP.clear();
                   modelL.clear();
                   modelAT.clear();
                   String queryP ="select * from PROJECT";
                   pst=con.prepareStatement(queryP);
                   rs = pst.executeQuery();

                while (rs.next()) { 
                    modelP.addElement(rs.getString("name"));  
                } 
                    
            } catch (Exception e) {  
            System.out.println("Retrieving Data Fail");  
            } finally {
                 try {
                 rs.close();
                 pst.close();
                //con.close();
                }
                 catch (Exception e) {
                 }
             }                 
     
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jPanel1 = new javax.swing.JPanel();
        jButton1 = new javax.swing.JButton();
        ImportDB = new javax.swing.JButton();
        jTextField1 = new javax.swing.JTextField();
        jTextField2 = new javax.swing.JTextField();
        jLabel4 = new javax.swing.JLabel();
        jScrollPane1 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();
        jLabel5 = new javax.swing.JLabel();
        jLabel6 = new javax.swing.JLabel();
        jLabel7 = new javax.swing.JLabel();
        jScrollPane5 = new javax.swing.JScrollPane();
        jList4 = new javax.swing.JList();
        jScrollPane6 = new javax.swing.JScrollPane();
        jList5 = new javax.swing.JList();
        jButton3 = new javax.swing.JButton();
        jPanel2 = new javax.swing.JPanel();
        jScrollPane2 = new javax.swing.JScrollPane();
        jList1 = new javax.swing.JList();
        jScrollPane3 = new javax.swing.JScrollPane();
        jList2 = new javax.swing.JList();
        jScrollPane4 = new javax.swing.JScrollPane();
        jList3 = new javax.swing.JList();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jLabel3 = new javax.swing.JLabel();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        jPanel1.setBorder(javax.swing.BorderFactory.createTitledBorder("Database import"));

        jButton1.setText("Select file");
        jButton1.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton1ActionPerformed(evt);
            }
        });

        ImportDB.setText("Import to DB");
        ImportDB.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                ImportDBActionPerformed(evt);
            }
        });

        jTextField1.setToolTipText("<html>Format of input ASCII file must be following:<br>X,Y<br>1,34<br>2,45<br>...</html>");

        jLabel4.setOpaque(true);
        jLabel4.setBackground(new java.awt.Color(216, 220, 241));
        jLabel4.setHorizontalAlignment(javax.swing.SwingConstants.CENTER);
        jLabel4.setText("<html>Enter name of <br><center>attribute</html>");
        jLabel4.setBorder(javax.swing.BorderFactory.createLineBorder(new java.awt.Color(0, 0, 0)));
        jLabel4.setOpaque(true);

        jScrollPane1.setBackground(new java.awt.Color(246, 244, 230));

        jTable1.setAutoCreateRowSorter(true);
        jTable1.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {
                {null, null},
                {null, null},
                {null, null},
                {null, null},
                {null, null},
                {null, null},
                {null, null},
                {null, null},
                {null, null},
                {null, null}
            },
            new String [] {
                "Column 1", "Column 2"
            }
        ) {
            Class[] types = new Class [] {
                java.lang.String.class, java.lang.String.class
            };
            boolean[] canEdit = new boolean [] {
                false, false
            };

            public Class getColumnClass(int columnIndex) {
                return types [columnIndex];
            }

            public boolean isCellEditable(int rowIndex, int columnIndex) {
                return canEdit [columnIndex];
            }
        });
        jTable1.setSelectionBackground(new java.awt.Color(255, 255, 185));
        jTable1.setSelectionForeground(new java.awt.Color(0, 0, 0));
        jTable1.setSelectionMode(javax.swing.ListSelectionModel.SINGLE_SELECTION);
        jScrollPane1.setViewportView(jTable1);

        jLabel5.setBackground(new java.awt.Color(216, 220, 241));
        jLabel5.setHorizontalAlignment(javax.swing.SwingConstants.CENTER);
        jLabel5.setText("<html><center>Select type of <br>data format for <br>db columns</center></html");
        jLabel5.setBorder(javax.swing.BorderFactory.createLineBorder(new java.awt.Color(0, 0, 0)));
        jLabel5.setOpaque(true);

        jLabel6.setBackground(new java.awt.Color(80, 113, 220));
        jLabel6.setForeground(new java.awt.Color(255, 255, 255));
        jLabel6.setText(" Column 1");
        jLabel6.setOpaque(true);

        jLabel7.setBackground(new java.awt.Color(80, 113, 220));
        jLabel7.setForeground(new java.awt.Color(255, 255, 255));
        jLabel7.setText(" Column 2");
        jLabel7.setOpaque(true);

        jList4.setModel(new javax.swing.AbstractListModel() {
            String[] strings = { "int", "float" };
            public int getSize() { return strings.length; }
            public Object getElementAt(int i) { return strings[i]; }
        });
        jList4.setToolTipText("Select type of data column 1 for input data. ");
        jList4.setVisibleRowCount(3);
        jScrollPane5.setViewportView(jList4);

        jList5.setModel(new javax.swing.AbstractListModel() {
            String[] strings = { "int", "float" };
            public int getSize() { return strings.length; }
            public Object getElementAt(int i) { return strings[i]; }
        });
        jList5.setToolTipText("Select type of data for column 2 input data");
        jList5.setVisibleRowCount(3);
        jScrollPane6.setViewportView(jList5);

        javax.swing.GroupLayout jPanel1Layout = new javax.swing.GroupLayout(jPanel1);
        jPanel1.setLayout(jPanel1Layout);
        jPanel1Layout.setHorizontalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jLabel5, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                    .addComponent(jButton1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(jLabel4, javax.swing.GroupLayout.DEFAULT_SIZE, 118, Short.MAX_VALUE)
                    .addComponent(ImportDB, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                    .addGroup(javax.swing.GroupLayout.Alignment.LEADING, jPanel1Layout.createSequentialGroup()
                        .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                            .addComponent(jTextField1, javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jTextField2, javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(jPanel1Layout.createSequentialGroup()
                                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                    .addComponent(jScrollPane5)
                                    .addComponent(jLabel6, javax.swing.GroupLayout.DEFAULT_SIZE, 154, Short.MAX_VALUE))
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                    .addComponent(jScrollPane6)
                                    .addComponent(jLabel7, javax.swing.GroupLayout.DEFAULT_SIZE, 147, Short.MAX_VALUE))))
                        .addGap(0, 40, Short.MAX_VALUE)))
                .addContainerGap())
        );
        jPanel1Layout.setVerticalGroup(
            jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel1Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jButton1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(jTextField1))
                .addGap(8, 8, 8)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jLabel4)
                    .addComponent(jTextField2))
                .addGap(18, 18, 18)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel6)
                    .addComponent(jLabel7))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                        .addComponent(jScrollPane6, javax.swing.GroupLayout.DEFAULT_SIZE, 56, Short.MAX_VALUE)
                        .addComponent(jScrollPane5, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE))
                    .addComponent(jLabel5, javax.swing.GroupLayout.PREFERRED_SIZE, 59, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addGap(18, 18, 18)
                .addGroup(jPanel1Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 99, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(ImportDB))
                .addContainerGap(14, Short.MAX_VALUE))
        );

        jButton3.setText("  Exit  ");
        jButton3.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButton3ActionPerformed(evt);
            }
        });

        jPanel2.setBorder(javax.swing.BorderFactory.createTitledBorder("Select database "));

        jList1.setSelectionMode(javax.swing.ListSelectionModel.SINGLE_SELECTION);
        jList1.setSelectionBackground(new java.awt.Color(255, 245, 185));
        jList1.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jList1MouseClicked(evt);
            }
        });
        jScrollPane2.setViewportView(jList1);

        jList2.setSelectionMode(javax.swing.ListSelectionModel.SINGLE_SELECTION);
        jList2.setSelectionBackground(new java.awt.Color(255, 245, 185));
        jList2.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                jList2MouseClicked(evt);
            }
        });
        jScrollPane3.setViewportView(jList2);

        jList3.setSelectionMode(javax.swing.ListSelectionModel.SINGLE_SELECTION);
        jList3.setSelectionBackground(new java.awt.Color(255, 245, 185));
        jList3.setVisibleRowCount(7);
        jScrollPane4.setViewportView(jList3);

        jLabel1.setBackground(new java.awt.Color(80, 113, 220));
        jLabel1.setForeground(new java.awt.Color(255, 255, 255));
        jLabel1.setText("  Project");
        jLabel1.setOpaque(true);

        jLabel2.setBackground(new java.awt.Color(80, 113, 220));
        jLabel2.setForeground(new java.awt.Color(255, 255, 255));
        jLabel2.setText("  Line");
        jLabel2.setOpaque(true);

        jLabel3.setBackground(new java.awt.Color(80, 113, 220));
        jLabel3.setForeground(new java.awt.Color(255, 255, 255));
        jLabel3.setText(" Type of attribute ");
        jLabel3.setOpaque(true);

        javax.swing.GroupLayout jPanel2Layout = new javax.swing.GroupLayout(jPanel2);
        jPanel2.setLayout(jPanel2Layout);
        jPanel2Layout.setHorizontalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jScrollPane2, javax.swing.GroupLayout.DEFAULT_SIZE, 150, Short.MAX_VALUE)
                    .addComponent(jLabel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addGap(25, 25, 25)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                    .addComponent(jScrollPane3, javax.swing.GroupLayout.DEFAULT_SIZE, 154, Short.MAX_VALUE)
                    .addComponent(jLabel2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE))
                .addGap(25, 25, 25)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane4, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                    .addComponent(jLabel3, javax.swing.GroupLayout.DEFAULT_SIZE, 129, Short.MAX_VALUE))
                .addContainerGap())
        );
        jPanel2Layout.setVerticalGroup(
            jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, jPanel2Layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(jLabel1)
                    .addComponent(jLabel2)
                    .addComponent(jLabel3))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(jPanel2Layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addComponent(jScrollPane2, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                    .addComponent(jScrollPane3, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                    .addComponent(jScrollPane4, javax.swing.GroupLayout.DEFAULT_SIZE, 128, Short.MAX_VALUE))
                .addContainerGap())
        );

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jPanel2, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addComponent(jPanel1, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, Short.MAX_VALUE)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addGap(0, 0, Short.MAX_VALUE)
                        .addComponent(jButton3, javax.swing.GroupLayout.PREFERRED_SIZE, 105, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addComponent(jPanel2, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jPanel1, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                .addGap(18, 18, Short.MAX_VALUE)
                .addComponent(jButton3)
                .addGap(12, 12, 12))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void jButton1ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton1ActionPerformed
        JFileChooser chooser = new JFileChooser();
        chooser.showOpenDialog(null);
        File f = chooser.getSelectedFile();
        m_filename = f.getAbsolutePath();
        //Document_path.setText(filename);
        jTextField1.setText(m_filename);
    }//GEN-LAST:event_jButton1ActionPerformed

    private void ImportDBActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_ImportDBActionPerformed
              
                // Check all selected fields 
                String col1;
                String col2;
                if (jList1.isSelectionEmpty()==true || jList2.isSelectionEmpty()==true || jList3.isSelectionEmpty()==true){
                JOptionPane.showMessageDialog(null,"Please select database");  
                return;
                    } else {
                String project = jList1.getSelectedValue().toString();             
                String line = jList2.getSelectedValue().toString();
                String attr = jList3.getSelectedValue().toString();
                }
                
                if (jTextField1.getText() == null || jTextField1.getText().equals("")){
                JOptionPane.showMessageDialog(null,"Please select file to import");  
                return;
                }
                if (jTextField2.getText() == null || jTextField2.getText().equals("")){
                JOptionPane.showMessageDialog(null,"Please input name of attribute"); 
                return;
                }
                if (jList4.isSelectionEmpty()==true || jList5.isSelectionEmpty()==true){
                JOptionPane.showMessageDialog(null,"Please select type of input data for column 1 and 2");
                return;
                    } else {
                 col1 = jList4.getSelectedValue().toString();
                 col2 = jList5.getSelectedValue().toString();
                }
              
                try{
                   con=DBConnect.ConnectDb();                   
                   String Line = jList2.getSelectedValue().toString();
                   String atype = jList3.getSelectedValue().toString();
                   String aname=jTextField2.getText();
                   String queryCheck="SELECT attr.name as atname, attr.id as aaid FROM attr LEFT JOIN line ON (attr.lid=line.id) INNER JOIN attr_type ON (attr.tid=attr_type.id ) WHERE line.name='"+Line+"' and attr_type.name='"+atype+"' and attr.name='"+aname+"'";
                   pst=con.prepareStatement(queryCheck);
                   rs = pst.executeQuery();
                   if(rs.next()){ 
                   int aaid=rs.getInt("aaid");
                   String aexist=rs.getString("atname");  
                   //JOptionPane.showMessageDialog(null,aaid+aexist);
                   if (aexist.equals(aname)){
                   if (JOptionPane.showConfirmDialog(null,"\n DB attribute: "+aname+" exist and will be overwrite\nAre you sure?") == JOptionPane.YES_OPTION) {                   
                   String queryDel="DELETE FROM attr WHERE id='"+aaid+"'";
                   pst=con.prepareStatement(queryDel);
                   pst.executeUpdate();} else { return;}
                   }}  
                   rs.close();
                   String queryP="SELECT * FROM line WHERE name='"+jList2.getSelectedValue().toString()+"'";
                    pst=con.prepareStatement(queryP);
                    rs = pst.executeQuery();
                    rs.next();
                    int lid=rs.getInt("id");
                    rs.close();
                    String queryA="SELECT * FROM attr_type WHERE name='"+jList3.getSelectedValue().toString()+"'";
                    pst=con.prepareStatement(queryA);
                    rs = pst.executeQuery();
                    rs.next();
                   int tid=rs.getInt("id");
                   rs.close();
                   String queryI="INSERT INTO attr (lid,tid,name) VALUES ('"+lid+"','"+tid+"','"+aname+"')";
                   pst=con.prepareStatement(queryI);
                   pst.executeUpdate();                  
                   String queryAID="SELECT attr.id as aid FROM attr LEFT JOIN line ON (attr.lid=line.id) INNER JOIN attr_type ON (attr.tid=attr_type.id ) WHERE line.name='"+Line+"' and attr_type.name='"+atype+"' and attr.name='"+aname+"'";
                   pst=con.prepareStatement(queryAID);
                   rs = pst.executeQuery();
                   rs.next();
                   int aid=rs.getInt("aid");
                   pst.close();
                   rs.close();              
                String tableName=jList3.getSelectedValue().toString()+"_LID"+lid+"_AID"+aid;
                // Drop existing table
                String queryDrop="DROP TABLE IF EXISTS "+tableName+"";
                pst=con.prepareStatement(queryDrop);
                pst.executeUpdate();                
  
                
                // create db table 
                String queryC="CREATE TABLE "+tableName+" (id INTEGER PRIMARY KEY AUTO_INCREMENT,aid int,x "+col1+",y "+col2+")";
                
                pst=con.prepareStatement(queryC);
                pst.executeUpdate();
                // Import CSV File
                String queryRead="INSERT INTO "+tableName+" (AID,X,Y) SELECT '"+aid+"',X,Y FROM csvread('"+m_filename+"')";
                pst=con.prepareStatement(queryRead);
                pst.executeUpdate();     
        }
        catch(Exception e){
            JOptionPane.showMessageDialog(null,e);

        }finally {
                 try {
               rs.close();
               pst.close();
                //con.close();
               }
                catch (Exception e) {
                        }
                }
                try{
                   String Line = jList2.getSelectedValue().toString();
                   String atype = jList3.getSelectedValue().toString();
                   String aname=jTextField2.getText();
                   String queryA="SELECT attr.id as aid, line.id as lid FROM attr LEFT JOIN line ON (attr.lid=line.id) INNER JOIN attr_type ON (attr.tid=attr_type.id ) WHERE line.name='"+Line+"' and attr_type.name='"+atype+"' and attr.name='"+aname+"'";
                   pst=con.prepareStatement(queryA);
                   rs = pst.executeQuery();
                   rs.next();
                   int aid=rs.getInt("aid");
                   int lid=rs.getInt("lid");
                   pst.close();
                   rs.close();              
                   String tableName=jList3.getSelectedValue().toString()+"_LID"+lid+"_AID"+aid;           
                   Update_table(tableName);
                  JOptionPane.showMessageDialog(null, "Insert data to database was successfully completed");
                } 
                catch(Exception e)
                {JOptionPane.showMessageDialog(null,e);}finally {
                     try {
                     rs.close();
                    pst.close();
                    //con.close();
                }
            catch (Exception e) {
                     }
             }
    }//GEN-LAST:event_ImportDBActionPerformed

    private void jButton3ActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButton3ActionPerformed
        dispose();
    }//GEN-LAST:event_jButton3ActionPerformed

    private void jList1MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jList1MouseClicked
          try {      
                    con=DBConnect.ConnectDb();
                    modelL.clear();
                    modelAT.clear();
                   String project = jList1.getSelectedValue().toString();
                   String queryL="SELECT line.name as name FROM line INNER JOIN project ON (project.id=line.pid ) WHERE project.name='"+project+"' ";
                   pst=con.prepareStatement(queryL);
                   rs = pst.executeQuery();
                   while (rs.next()) { 
                   modelL.addElement(rs.getString("name"));  
                }           
            
        } catch (Exception e) {  
            System.out.println("Retrieving Data Fail");  
            } finally {
                 try {
                 rs.close();
                 pst.close();
                //con.close();
                }
                 catch (Exception e) {
                 }
             }        
    }//GEN-LAST:event_jList1MouseClicked

    private void jList2MouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_jList2MouseClicked
                   try {
                   con=DBConnect.ConnectDb();
                   modelAT.clear();
                   String queryA ="select NAME from attr_type";
                   pst=con.prepareStatement(queryA);
                   rs = pst.executeQuery();
                   while (rs.next()) { 
                   modelAT.addElement(rs.getString("name"));  
                }
        }catch (Exception e) {  
            System.out.println("Retrieving Data Fail");  
            } finally {
                 try {
                 rs.close();
                 pst.close();
                //con.close();
                }
                 catch (Exception e) {
                 }
             }   
    }//GEN-LAST:event_jList2MouseClicked
private void Update_table(String tableName){
     try{
     con=DBConnect.ConnectDb();
    String query = "SELECT x,y FROM "+tableName+"";
    pst=con.prepareStatement(query);
    rs=pst.executeQuery();
    jTable1.setModel(DbUtils.resultSetToTableModel(rs));
   
   }
    catch(Exception e){
    JOptionPane.showMessageDialog(null, e);
   } finally {
     try {
     rs.close();
     pst.close();
     //con.close();
    }
   catch (Exception e) {
    }
   }
}
    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        /* Set the Nimbus look and feel */
        //<editor-fold defaultstate="collapsed" desc=" Look and feel setting code (optional) ">
        /* If Nimbus (introduced in Java SE 6) is not available, stay with the default look and feel.
         * For details see http://download.oracle.com/javase/tutorial/uiswing/lookandfeel/plaf.html 
         */
        try {
            for (javax.swing.UIManager.LookAndFeelInfo info : javax.swing.UIManager.getInstalledLookAndFeels()) {
                if ("Nimbus".equals(info.getName())) {
                    javax.swing.UIManager.setLookAndFeel(info.getClassName());
                    break;
                }
            }
        } catch (ClassNotFoundException ex) {
            java.util.logging.Logger.getLogger(DBImport.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (InstantiationException ex) {
            java.util.logging.Logger.getLogger(DBImport.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (IllegalAccessException ex) {
            java.util.logging.Logger.getLogger(DBImport.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        } catch (javax.swing.UnsupportedLookAndFeelException ex) {
            java.util.logging.Logger.getLogger(DBImport.class.getName()).log(java.util.logging.Level.SEVERE, null, ex);
        }
        //</editor-fold>

        /* Create and display the form */
        java.awt.EventQueue.invokeLater(new Runnable() {
            public void run() {
                new DBImport().setVisible(true);
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton ImportDB;
    private javax.swing.JButton jButton1;
    private javax.swing.JButton jButton3;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JLabel jLabel4;
    private javax.swing.JLabel jLabel5;
    private javax.swing.JLabel jLabel6;
    private javax.swing.JLabel jLabel7;
    private javax.swing.JList jList1;
    private javax.swing.JList jList2;
    private javax.swing.JList jList3;
    private javax.swing.JList jList4;
    private javax.swing.JList jList5;
    private javax.swing.JPanel jPanel1;
    private javax.swing.JPanel jPanel2;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JScrollPane jScrollPane3;
    private javax.swing.JScrollPane jScrollPane4;
    private javax.swing.JScrollPane jScrollPane5;
    private javax.swing.JScrollPane jScrollPane6;
    private javax.swing.JTable jTable1;
    private javax.swing.JTextField jTextField1;
    private javax.swing.JTextField jTextField2;
    // End of variables declaration//GEN-END:variables
    private DefaultListModel modelP; 
    private DefaultListModel modelL;
    private DefaultListModel modelAT;
    private String m_filename;
    private String m_val1;
}
